package com.unsky.utils.jpasql;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.Parameter;
import javax.persistence.Query;
import javax.persistence.Table;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.util.StringUtils;

import com.unsky.utils.vo.PageVO;
/**
 * JPA 查询工具类
 * @project   xmlsql
 * @author    unsky
 * @date      2017年1月7日 上午9:09:45
 * @copyRight 山东阿帕网络技术有限公司
 * @webSite   http://www.kt56.com
 */
public class JPAQueryUtil{
	private XmlRead xmlRead=XmlRead.getInstance();
	
	private EntityManager entityManager;
	public JPAQueryUtil(EntityManager entityManager) {
		this.entityManager=entityManager;
	}
	/**
	 * 分页查询
	 * @param sqlId
	 * @param entityClass  返回的类型
	 * @param query  调用conditions 封装查询参数
	 * @param pagerModel 分页model
	 * @return
	 * @throws Exception
	 * @Date unsky 2017年1月7日 上午8:43:44
	 */
	public <T> Page<T> queryForPage(String sqlId,Class<T> entityClass, 
			XmlQuery query,PageVO pagerModel) throws Exception{
		List<T> list=new ArrayList<T>();
		Long count =0l;
		if(query!=null){
			count = queryForObject(sqlId+"Count", Long.class, query);//查询总条数
		}else{
			count = queryForObject(sqlId+"Count", Long.class);//查询总条数
		}
		if(count>0){
			XmlSql xmlQuery = pacConditions(entityManager, sqlId, entityClass, query);
			Query jpaQuery =xmlQuery.getQuery();
			jpaQuery.setFirstResult((pagerModel.getPage()-1)*pagerModel.getPageSize());
			jpaQuery.setMaxResults(pagerModel.getPageSize());
			List list1 = jpaQuery.getResultList();
			if(list1.size()>0&&(list1.get(0) instanceof Map)){
				for (Object obj : list1) {
					obj=doSqlResult(obj,entityClass);
					list.add((T) obj);
				}
			}else{
				list=list1;
			}
			entityManager.close();
		}
		return new PageImpl<T>(list, pagerModel.getPageable(), count);
	}
	/**
	 * 分页查询 
	 * @param sqlId
	 * @param entityClass  返回的类型
	 * @param map  参数Map
	 * @param pagerModel 分页model
	 * @return
	 * @throws Exception
	 * @Date unsky 2017年1月7日 上午8:44:39
	 */
	public <T> Page<T> queryForPage(String sqlId,Class<T> entityClass, 
			Map map,PageVO pagerModel) throws Exception{
		List<T> list=new ArrayList<T>();
		Long count= queryForObject(sqlId+"Count", Long.class, map);//查询总条数
		if(count>0){
			XmlSql xmlQuery = pacConditions(entityManager, sqlId, entityClass, map);
			Query jpaQuery =xmlQuery.getQuery();
			jpaQuery.setFirstResult((pagerModel.getPage()-1)*pagerModel.getPageSize());
			jpaQuery.setMaxResults(pagerModel.getPageSize());
			List list1 = jpaQuery.getResultList();
			if(list1.size()>0&&(list1.get(0) instanceof Map)){
				for (Object obj : list1) {
					obj=doSqlResult(obj,entityClass);
					list.add((T) obj);
				}
			}else{
				list=list1;
			}
			entityManager.close();
		}
		return new PageImpl<T>(list, pagerModel.getPageable(), count);
	}
	/**
	 * 分页查询
	 * @param sqlId
	 * @param entityClass
	 * @param pagerModel
	 * @return
	 * @throws Exception
	 * @Date unsky 2017年1月7日 上午9:01:54
	 */
	public <T> Page<T> queryForPage(String sqlId,Class<T> entityClass, 
			PageVO pagerModel) throws Exception{
		return queryForPage(sqlId, entityClass,new HashMap(), pagerModel);
	}
	/**
	 * 查询列表
	 * @param sqlId
	 * @param entityClass
	 * @return
	 * @throws Exception
	 */
	public <T> List<T> queryForList(String sqlId,Class<T> entityClass) 
			throws Exception{
		return queryForList(sqlId, entityClass,new HashMap<Object, Object>());
	}
	/**
	 * 查询列表
	 * @param sqlId
	 * @param entityClass 查询返回类型
	 * @param query  调用conditions 封装查询参数
	 * @return
	 * @throws Exception
	 * @Date unsky 2017年1月7日 上午9:02:11
	 */
	public <T> List<T> queryForList(String sqlId,Class<T> entityClass,XmlQuery query)
			throws Exception{
		XmlSql xmlQuery = pacConditions(entityManager, sqlId, entityClass, query);
		Query jpaQuery =xmlQuery.getQuery();
		List<T> list = jpaQuery.getResultList();
		if(list.size()>0&&(list.get(0) instanceof Map)){//判断是不是数组
			List<T> list1=new ArrayList<T>();
			for (Object obj : list) {
				obj=doSqlResult(obj,entityClass);
				list1.add((T) obj);
			}
		}
		entityManager.close();
		return list;
	}
	/**
	 * 查询列表
	 * @param sqlId
	 * @param entityClass 查询返回数据类型
	 * @param map  参数map
	 * @return
	 * @throws Exception
	 * @Date unsky 2017年1月7日 上午9:03:59
	 */
	public <T> List<T> queryForList(String sqlId,Class<T> entityClass,Map map)
			throws Exception{
		XmlSql xmlQuery = pacConditions(entityManager, sqlId, entityClass, map);
		Query jpaQuery =xmlQuery.getQuery();
		List<T> list = jpaQuery.getResultList();
		if(list.size()>0&&(list.get(0) instanceof Map)){//判断是不是数组
			List<T> list1=new ArrayList<T>();
			for (Object obj : list) {
				obj=doSqlResult(obj,entityClass);
				list1.add((T) obj);
			}
		}
		entityManager.close();
		return list;
	}
	
	/**
	 * 查询对象
	 * @param sqlId
	 * @param entityClass 返回的数据类型
	 * @return
	 * @throws Exception
	 * @Date unsky 2017年1月7日 上午9:07:49
	 */
	public <T> T queryForObject(String sqlId,Class<T> entityClass)
			throws Exception{
		return queryForObject(sqlId, entityClass,new HashMap<>());
	}
	/**
	 * 查询对象
	 * @param sqlId
	 * @param entityClass 返回的数据类型
	 * @param query   调用conditions 封装查询参数
	 * @return
	 * @throws Exception
	 * @Date unsky 2017年1月7日 上午9:08:04
	 */
	public <T> T queryForObject(String sqlId,Class<T> entityClass,XmlQuery query)
			throws Exception{
		XmlSql xmlQuery = pacConditions(entityManager, sqlId, entityClass, query);
		Query jpaQuery =xmlQuery.getQuery();
		jpaQuery.setMaxResults(1);//最大之查询一条 防止返回多条记录
		Object obj = jpaQuery.getSingleResult();
		if(obj instanceof Map){
			obj=doSqlResult(obj,entityClass);
		}
		entityManager.close();
		return (T)obj;  
	}
	/**
	 * 查询对象
	 * @param sqlId
	 * @param entityClass 返回的数据类型
	 * @param map 参数
	 * @return
	 * @throws Exception
	 * @Date unsky 2017年1月7日 上午9:08:39
	 */
	public <T> T queryForObject(String sqlId,Class<T> entityClass,Map map)
			throws Exception{
		XmlSql xmlQuery = pacConditions(entityManager, sqlId, entityClass, map);
		Query jpaQuery =xmlQuery.getQuery();
		jpaQuery.setMaxResults(1);//最大之查询一条 防止返回多条记录
		List<T> list= jpaQuery.getResultList();
		if(list==null||list.isEmpty()){
			return null;
		}
		Object obj=list.get(0);
		if(obj instanceof Map){
			obj=doSqlResult(obj,entityClass);
		}
		entityManager.close();
		return (T)obj;  
	}
	/**
	 * 转化字符数组成实体
	 * @param obj
	 * @param entityClass
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 * @throws SecurityException
	 */
	private <T> T doSqlResult(Object obj,Class<T> entityClass)
			throws InstantiationException, IllegalAccessException, 
			NoSuchFieldException, SecurityException {
		if(entityClass==Map.class||entityClass==HashMap.class){
			return (T)obj;
		}
		return mapToObj((Map)obj, entityClass);
	}
	/**
	 * 组装查询公共步骤
	 * >>1 根据ID 查出SQl
	 * >>2 根据参数是否为空分段处理
	 * >>3 根据Sql类型分段处理
	 * @param entityManager
	 * @param sqlId
	 * @param entityClass
	 * @param query
	 * @return
	 * @throws Exception 
	 */
	private <T>XmlSql pacConditions(EntityManager entityManager,String sqlId,
			Class<T> entityClass,Object queryContion) throws Exception{
		Map<String, Object> params = new HashMap<String, Object>();//查询参数
		Query jpaQuery=null;
		Map<String, Object> conditionsMap=null;
		Set<String> keys =null;
		if(queryContion!=null){
			if(queryContion instanceof XmlQuery){
				XmlQuery query = (XmlQuery) queryContion;
				conditionsMap=query.conditions();
			}else{
				conditionsMap=(Map<String, Object>) queryContion;
			}
			keys = conditionsMap.keySet();
			for (String key : keys) {
				params.put("#"+key, conditionsMap.get(key));
			}
		}
		XmlSql xmlSql=getQuerySql(sqlId, params);
		if(XmlElement.JHQL.equals(xmlSql.getSqlType())){
			jpaQuery= entityManager.createQuery(xmlSql.getSql().toString(),entityClass);
		}else{
			if(entityClass.getAnnotation(Table.class)!=null){
				jpaQuery = entityManager.createNativeQuery(xmlSql.getSql().toString(), entityClass);
			}else{
				jpaQuery = entityManager.createNativeQuery(xmlSql.getSql().toString());
				jpaQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			}
		}
		List<String> parameters=new ArrayList<String>();//参数列表
		Set<Parameter<?>> set = jpaQuery.getParameters();
		for (Parameter<?> parameter : set) {
			parameters.add(parameter.getName().trim());
		}
		if(keys!=null){
			for (String key : keys) {
				if(StringUtils.isEmpty(conditionsMap.get(key))){
					continue;
				}
				if(parameters.contains(key)){
					jpaQuery.setParameter(key, conditionsMap.get(key));
				}
			}
		}
		xmlSql.setQuery(jpaQuery);
		return xmlSql;
	}
	public  XmlSql getQuerySql(String id) throws Exception{
		return xmlRead.getSql(id, new HashMap<String,Object>());
	}
	public  XmlSql getQuerySql(String id,Map<String,Object> params ) throws Exception{
		return xmlRead.getSql(id, params);
	}
	private <T> T mapToObj(Map map,Class<T> clazz) 
			throws InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException{
		Set<Object> keys = map.keySet();
		if(clazz==Integer.class||clazz==Double.class
				||clazz==Long.class||clazz==Float.class
				||clazz==Short.class||clazz==Byte.class){
			for (Object object : keys) {
				if(map.get(object) instanceof Number){
					return (T)baseTypeDeal((Number) map.get(object), clazz);
				}
				return  (T)map.get(object);
			}
		}
		if(clazz==String.class){
			for (Object object : keys) {
				return  (T)map.get(object);
			}
		}
		T obj = clazz.newInstance();
		
		for (Object object : keys) {
			Field field = clazz.getDeclaredField(object.toString());
			if(field!=null){
				field.setAccessible(true);
				if(map.get(object) instanceof Number){
					field.set(obj, baseTypeDeal((Number) map.get(object), field.getType()));
				}else{
					field.set(obj, map.get(object));
				}
				
			}
			
		}
		return (T)obj;
	}
	
	private <T> Object baseTypeDeal(Number number,Class<T> clazz){
		if(clazz==Integer.class){
			return number.intValue();
		}else if(clazz==Double.class){
			return number.doubleValue();
		}else if(clazz==Long.class){
			return number.longValue();
		}else if(clazz==Float.class){
			return number.floatValue();
		}else if(clazz==Short.class){
			return number.shortValue();
		}else if(clazz==Byte.class){
			return number.byteValue();
		}
		return number;
	}
}
